package com.niit.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DbUtil{
	
	private static DataSource ds ;
	
	static{
		ds = new ComboPooledDataSource();
	}
	
	private Connection getConnection(){
		Connection conn = null;
		try {
			conn = ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	/**
	 * 执行insert、update、delete语句
	 * @param sql sql语句
	 * @param objects 边长参数
	 * @return 受影响行数
	 */
	public int update(String sql,Object...objects){
		int result = 0;
		try {
			result = new QueryRunner(ds,true).update(sql, objects);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return result;
	}
	
	/**
	 * 单表查询，将结果封装到bean中
	 * @param sql sql语句
	 * @param cls 要查询的表对应的Bean类的字节码文件：如User.class
	 * @param objects 边长参数
	 * @return 多个bean对象的List集合
	 */
	public <T> List<T> select(String sql,Class<T> cls,Object...objects){
		List<T> list = null;
		try {
			list = new QueryRunner(ds,true).query(sql, new BeanListHandler<T>(cls), objects);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 查询count(*)
	 */
	public int selectCount(String sql,Object...obj){
		int count = 0;
		PreparedStatement ps=null;
		Connection conn=null;
		ResultSet rs=null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0;i<obj.length;i++){
				ps.setObject(i+1, obj[i]);
			}
			rs = ps.executeQuery();
			if(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(conn, rs, ps);
		}
		return count;  
	}
	
	/**
	 * 多表查询，将每行数据封装到Map中，然后将map添加到List中
	 * @param sql sql语句
	 * @param objects 变长参数
	 * @return List集合
	 */
	public <T> List<Map<String, Object>> select(String sql,Object...objects){
		PreparedStatement ps=null;
		Connection conn=null;
		ResultSet rs=null;
		List<Map<String, Object>> list = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0;i<objects.length;i++){
				ps.setObject(i+1, objects[i]);
			}
			rs = ps.executeQuery();
			MapListHandler handler = new MapListHandler();
			list = handler.handle(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(conn,rs,ps);
		}
		return list;
	}
	
	/**
	 * 关闭连接资源 connection resultset statement
	 */
	private void close(Connection conn,ResultSet rs,Statement st) {
		DbUtils.closeQuietly(conn, st, rs);
	}
	
	/**
	 * 多表查询，将每行数据封装到数组中，然后将数组添加到List中
	 * @param sql sql语句
	 * @param objects 变长参数
	 * @return List集合
	 */
	public <T> List<Object[]> selectArray(String sql,Object...objects){
		PreparedStatement ps=null;
		Connection conn=null;
		ResultSet rs=null;
		List<Object[]> list = new ArrayList<Object[]>();
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0;i<objects.length;i++){
				ps.setObject(i+1, objects[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while(rs.next()){
				Object[] objs = new Object[columnCount];
				for(int i=0;i<columnCount;i++){
					objs[i] = rs.getObject(i+1);
				}
				list.add(objs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(conn,rs,ps);
		}
		return list;
	}
}
